-- 音乐数据库表 DDL 语句 (PostgreSQL版本)

-- 创建音乐表
CREATE TABLE music (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    artist VARCHAR(255) NOT NULL,
    album VARCHAR(255),
    duration INTEGER,
    genre VARCHAR(100),
    release_date DATE,
    file_path VARCHAR(500),
    file_size BIGINT,
    file_format VARCHAR(20),
    bitrate INTEGER,
    sample_rate INTEGER,
    cover_image_url VARCHAR(500),
    lyrics TEXT,
    composer VARCHAR(255),
    lyricist VARCHAR(255),
    publisher VARCHAR(255),
    copyright TEXT,
    track_number INTEGER,
    disc_number INTEGER,
    language VARCHAR(50),
    country VARCHAR(50),
    is_favorite BOOLEAN DEFAULT FALSE,
    play_count INTEGER DEFAULT 0,
    last_played TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引以提高查询性能
CREATE UNIQUE INDEX idx_music_file_path ON music(file_path);
CREATE INDEX idx_music_title ON music(title);
CREATE INDEX idx_music_artist ON music(artist);
CREATE INDEX idx_music_album ON music(album);
CREATE INDEX idx_music_genre ON music(genre);
CREATE INDEX idx_music_release_date ON music(release_date);
CREATE INDEX idx_music_created_at ON music(created_at);
CREATE INDEX idx_music_title_artist ON music(title, artist);
CREATE INDEX idx_music_artist_album ON music(artist, album);
CREATE INDEX idx_music_genre_release ON music(genre, release_date);
CREATE INDEX idx_music_favorite ON music(is_favorite);
CREATE INDEX idx_music_play_count ON music(play_count);

-- 创建更新时间自动更新函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 创建触发器
CREATE TRIGGER update_music_updated_at BEFORE UPDATE
ON music FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 创建艺术家表(可选，用于更规范的数据结构)
CREATE TABLE artist (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    alias TEXT,
    introduction TEXT,
    avatar_url VARCHAR(500),
    country VARCHAR(50),
    birth_date DATE,
    debut_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建艺术家表索引
CREATE UNIQUE INDEX idx_artist_name ON artist(name);
CREATE INDEX idx_artist_created_at ON artist(created_at);

-- 创建触发器更新艺术家表的updated_at字段
CREATE TRIGGER update_artist_updated_at BEFORE UPDATE
ON artist FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 创建专辑表(可选，用于更规范的数据结构)
CREATE TABLE album (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    artist_id BIGINT,
    artist_name VARCHAR(255) NOT NULL,
    release_date DATE,
    publisher VARCHAR(255),
    cover_image_url VARCHAR(500),
    introduction TEXT,
    genre VARCHAR(100),
    language VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建专辑表索引
CREATE UNIQUE INDEX idx_album_title_artist ON album(title, artist_name);
CREATE INDEX idx_album_artist_id ON album(artist_id);
CREATE INDEX idx_album_release_date ON album(release_date);
CREATE INDEX idx_album_created_at ON album(created_at);

-- 创建触发器更新专辑表的updated_at字段
CREATE TRIGGER update_album_updated_at BEFORE UPDATE
ON album FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 创建播放列表表(可选)
CREATE TABLE playlist (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    cover_image_url VARCHAR(500),
    is_public BOOLEAN DEFAULT FALSE,
    play_count INTEGER DEFAULT 0,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建播放列表表索引
CREATE INDEX idx_playlist_name ON playlist(name);
CREATE INDEX idx_playlist_created_by ON playlist(created_by);
CREATE INDEX idx_playlist_created_at ON playlist(created_at);

-- 创建触发器更新播放列表表的updated_at字段
CREATE TRIGGER update_playlist_updated_at BEFORE UPDATE
ON playlist FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 创建播放列表音乐关联表(可选)
CREATE TABLE playlist_music (
    id BIGSERIAL PRIMARY KEY,
    playlist_id BIGINT NOT NULL,
    music_id BIGINT NOT NULL,
    sort_order INTEGER DEFAULT 0,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_playlist_music_playlist_id FOREIGN KEY (playlist_id) REFERENCES playlist(id) ON DELETE CASCADE,
    CONSTRAINT fk_playlist_music_music_id FOREIGN KEY (music_id) REFERENCES music(id) ON DELETE CASCADE
);

-- 创建播放列表音乐关联表索引
CREATE UNIQUE INDEX idx_playlist_music_unique ON playlist_music(playlist_id, music_id);
CREATE INDEX idx_playlist_music_playlist_id ON playlist_music(playlist_id);
CREATE INDEX idx_playlist_music_music_id ON playlist_music(music_id);